package com.hezhi.framework.dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.hezhi.framework.dao.DAOUtil;
import com.hezhi.framework.exception.DaoException;

/**
 *     a static facade class to encapsulate native JDBC API.
 */
public abstract class JdbcOprt {
	/**
	 * query the first record,then extractor the record to return a Object &lt;T&gt; 
	 * @param sql
	 * @param extractor
	 * @param args
	 * @return
	 * @throws Exception 
	 */
	public static <T> T get(Connection conn,final String sql, final Extractor<T> extractor, Object... args) {
		ArgSetter argSetter = new ArgSetter(args);
		PreparedStatement ps = null;
		ResultSet rs = null;//NOSONAR closed by DAOUtil in the finaly cause.
		try {
			//Ignore the security issue. only simple encapsulation of JDBC API
			ps = conn.prepareStatement(sql);//NOSONAR
			argSetter.setValues(ps);
			rs = ps.executeQuery();
			return rs.next() ? extractor.extract(rs) : null;
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs,ps,null);
		}
	}
	
	/**
	 * query all records,then extract record to object &lt;T&gt;, return a List&lt;T&gt;  
	 * @param sql
	 * @param extractor
	 * @param args
	 * @return
	 * @throws Exception 
	 */
	public static <T> List<T> list(Connection conn,final String sql, Extractor<T> extractor, Object... args) {
		ArgSetter argSetter = new ArgSetter(args);
		PreparedStatement ps = null;
		ResultSet rs = null;//NOSONAE, closed by DAOUtil
		try {
			//Ignore the security issue. only simple encapsulation of JDBC API
			ps = conn.prepareStatement(sql);//NOSONAR
			argSetter.setValues(ps);
			rs = ps.executeQuery();
			List<T> list = new ArrayList<T>();
			while (rs.next()) {
				list.add(extractor.extract(rs));
			}
			return list;
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs,ps,null);
		}
	}
	
	/**
	 * query a page of records, then extract record to object &lt;T&gt;, return a List&lt;T&gt;  
	 * @param <T>
	 * @param pageNo begin with 0
	 * @param pageSize
	 * @param sql
	 * @param extractor
	 * @param args
	 * @return
	 */
	public static <T> List<T> page(Connection conn,int pageNo, int pageSize, final String sql, Extractor<T> extractor,
	        Object... args) {
		ArgSetter argSetter = new ArgSetter(args);
		PreparedStatement ps = null;
		ResultSet rs = null;//NOSONAE, closed by DAOUtil
		try {
			//Ignore the security issue. only simple encapsulation of JDBC API
			ps = conn.prepareStatement(sql);//NOSONAR
			argSetter.setValues(ps);
			rs = ps.executeQuery();
			List<T> list = new ArrayList<T>();

			for (int i = 0; rs.next(); i++) {
				if (i < pageNo * pageSize) {
					continue;
				}
				list.add(extractor.extract(rs));
				if (i > pageNo * pageSize + pageSize - 2) {
					break;
				}
			}
			return list;
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(rs,ps,null);
		}
	}
	
	/**
	 * update,insert,delete
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int execute(Connection conn,final String sql) {
		PreparedStatement ps = null;
		try {
			//Ignore the security issue. only simple encapsulation of JDBC API
			ps = conn.prepareStatement(sql);//NOSONAR
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(null,ps,null);
		}
	}

	/**
	 * update,insert,delete
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int execute(Connection conn,final String sql, Object... args) {
		PreparedStatement ps = null;
		final ArgSetter pss = new ArgSetter(args);
		try {
			//Ignore the security issue. only simple encapsulation of JDBC API
			ps = conn.prepareStatement(sql);//NOSONAR
			pss.setValues(ps);
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(null,ps,null);
		}
	}
	/**
	 * batch Execute
	 * @param sql
	 * @param batchArgs
	 * @return
	 */
	public static int[] batchExecute(Connection conn,final String sql, Object[]... batchArgs) {
		final ArgSetter argSetter = new ArgSetter(batchArgs);
		PreparedStatement ps = null;
		try {
			//Ignore the security issue. only simple encapsulation of JDBC API
			ps = conn.prepareStatement(sql);//NOSONAR
			int batchSize = argSetter.getBatchSize();
			for (int i = 0; i < batchSize; i++) {
				argSetter.setValues(ps, i);
				ps.addBatch();
			}
			return ps.executeBatch();
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			DAOUtil.closeAllResource(null,ps,null);
		}
	}

	/**
	 * get sequence form DB by sequence name. 
	 * @param sequenceName
	 * @return
	 * @throws Exception 
	 */
	public static long sequence(Connection conn,String sequenceName) {
		final String sql = "SELECT " + sequenceName + ".NEXTVAL FROM DUAL";
		long seqValue = get(conn,sql, new Extractor<Long>() {
			public Long extract(ResultSet resultSet) throws SQLException {
				return resultSet.getLong("NEXTVAL");
			}
		});
		return seqValue;
	}
	
	/**
	 * the keys of the Map just is the arguments keys.
	 * <br /> the value mapped with the key is extracted form the record related with the key of DB.
	 * @param <K>
	 * @param <V>
	 * @param sql
	 * @param extractor
	 * @param keys
	 * 	the last argument
	 * @param args
	 * @return
	 * @throws Exception 
	 */
	public static <K, V> Map<K, V> map(Connection conn,final String sql, Extractor<V> extractor, Collection<K> keys,
	        Object... args) {
		Map<K, V> map = new HashMap<K, V>();
		for (K k : keys) {
			Object[] newArgs = new Object[args.length + 1];
			for (int i = 0; i < newArgs.length; i++) {
				if (i < args.length) {
					newArgs[i] = args[i];
				} else {
					newArgs[i - args.length] = k;
				}
			}
			map.put(k, get(conn,sql, extractor, newArgs));
		}
		return map;
	}
}
